﻿/******************************************************   
创建者: 梁家健
创建时间: 2017-06-02
修改:
修改时间: 
功能: 调拨作业
 ******************************************************/

using com.nm.ApiServer;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using com.nm.library.Helper;
using george.Data;
using Nancy.Extensions;
using NHibernateVertica;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using com.nm.library;
using CommodityTransferAPI.model;

namespace CommodityTransferAPI
{
    //[MustLogin]
    [Description("调拨作业")]
    public class NewOdsErpTmpCalcStockHandler : BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();

        #region GetOdsErpTmpCalcStock

        /// Writer：梁家健
        /// Create Date：2017-06-02
        [MustLogin]
        [Description("调拨作业列表")]
        [ReturnType(typeof(PaperData<NewOdsErpTmpCalcStockModel.ShowModel>))]
        public ApiResult GetOdsErpTmpCalcStock([ParamDescription("货号")] string MaterialID,
            [ParamDescription("店铺")] string ShopIDs = "")
        {
            try
            {
                if (string.IsNullOrEmpty(MaterialID)) return Error("请输入货号！");
                var shopList = string.IsNullOrEmpty(ShopIDs)
                    ? ""
                    : string.Join(",", ShopIDs.Split(',').Select(item => "'" + item + "'"));
                var title = ReturnTitle(MaterialID);
                var seriesItem = ReturnSeriesItem(MaterialID);
                var query = ReturnOdsErpTmpCalcStockList(MaterialID, shopList, DateTime.Now).GroupBy(d => new
                {
                    d.StockID,
                    d.StockDate,
                    d.MaterialID,
                    d.ShopID
                }).ToList();
                var newList = query.Select(d => new NewOdsErpTmpCalcStockModel.GroupModel
                {
                    list =
                        d.GroupBy(
                                r =>
                                    r.StockID + "_" + r.StockDate + "_" + r.MaterialID + "_" + r.ShopID + "_" +
                                    r.OutStockID)
                            .ToList(),
                }).ToList();
                newList.ForEach(x =>
                {
                    x.list.ToList().ForEach(y =>
                    {
                        y.ToList().ForEach(z =>
                        {
                            z.CirculWeek = z.WeekSaleQty == 0
                                ? 0
                                : Math.Round((decimal)y.Sum(d => d.CanUseQty) / (decimal)z.WeekSaleQty, 1);
                        });
                    });
                });
                var result = new NewOdsErpTmpCalcStockModel.ShowModel
                {
                    title = title,
                    group = newList,
                    SeriesItem = seriesItem,
                };
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        #endregion

        #region ReturnOdsErpTmpCalcStockList

        /// <summary>
        /// 返回调拨作业列表
        /// </summary>
        /// <param name="MaterialID"></param>
        /// <param name="ShopList"></param>
        /// <param name="Date"></param>
        /// <param name="Page"></param>
        /// <param name="Size"></param>
        /// <returns></returns>
        public IList<NewOdsErpTmpCalcStockModel.ListModel> ReturnOdsErpTmpCalcStockList(string MaterialID, string ShopList, DateTime Date)
        {
            try
            {
                var whereStr = "";
                //非管理员按用户ID筛选数据
                if (!MustAdminAttribute.Check(UserInfo.UserID))
                    whereStr += " and Operator=" + UserInfo.UserID;
                var sql = $@"SELECT  table2.* ,maxsize.SizeID ,ISNULL(RequireQty.MoveRequireQty,0) as MoveRequireQty, ( CASE WHEN stock.CanUseQty IS NULL THEN 0 WHEN stock.CanUseQty < 0 THEN 0 ELSE stock.CanUseQty END ) AS CanUseQty , ISNULL(stock.PathQty,0) as PathQty , ( ( CASE WHEN stock.CanUseQty IS NULL THEN 0  WHEN stock.CanUseQty < 0 THEN 0 ELSE stock.CanUseQty END ) + ISNULL(stock.PathQty,0) ) AS SumQty , ISNULL(table3.SizeName, maxsize.SizeName) AS SizeName , table3.SizeCoreFlag , ISNULL(outStock.ExcelFlag,ISNULL(inStock.ExcelFlag,0)) as ExcelFlag , ( CASE WHEN outStock.OutStockID IS NULL THEN ( CASE WHEN inStock.InStockID IS NULL THEN 0  ELSE 1  END )  ELSE 2 END ) AS InOrOutStock , inStock.OutStockID ,  inStock.ShopID AS OutShopID , inStock.ShopName AS OutShopName ,( CASE WHEN inStock.CanUseQty IS NULL THEN 0 WHEN inStock.CanUseQty < 0 THEN 0 ELSE inStock.CanUseQty END ) AS NewCanUseQty
FROM ( 
    SELECT    *
    FROM ( 
            SELECT stock.CompanyID  , stock.ExtrSource ,  stock.StockID ,  stock.MaterialID , stock.StockDate , shop.ShopID ,  shop.ShopName , shop.AreaID ,  material.MaterialName ,  rate.CalcSaleRate ,  rate.WeekSaleQty , rate.MoveOutFlag ,  rate.SKCCzpFlag , rate.CzpOrderNum , rate.ISbreakSize , rate.SuppleDate , rate.ISbreakSize_light,rate.Saledays
            FROM (
                    SELECT CompanyID ,ExtrSource, StockID ,MaterialID ,StockDate  FROM nods.nods_erp_TmpCalcStock
                    WHERE CompanyID = '{UserInfo.UserCompanyId}' and MaterialID = '{MaterialID}' AND StockDate = '{Date.AddDays(-1):yyyy-MM-dd}' AND IsShop = 1
                    GROUP BY  CompanyID ,ExtrSource,  StockID ,  MaterialID ,  StockDate
                  ) stock
             INNER JOIN ( 
                            SELECT CompanyID ,  ShopID ,ShopName , StockID , AreaID   FROM   ndw.ndw_Shop 
                            WHERE CompanyID = '{UserInfo.UserCompanyId}' and  1 = 1 {(string.IsNullOrEmpty(ShopList) ? "" : $@"AND ShopID in ({ShopList})")} 
                          ) shop ON stock.CompanyID = shop.CompanyID  AND stock.StockID = shop.StockID
             INNER JOIN ( 
                            SELECT ExtrSource,MaterialID , MaterialName FROM nods.nods_erp_SD_Mat_Material
                            WHERE ExtrSource = '{UserInfo.UserExtrSource}' and  MaterialID = '{MaterialID}'
                          ) material  ON stock.ExtrSource = material.ExtrSource and stock.MaterialID = material.MaterialID
             INNER JOIN (
                            SELECT a.CompanyID , a.StockID , a.MaterialID , a.CalcSaleRate , a.WeekSaleQty , ISNULL(b.MoveOutFlag, 0) AS MoveOutFlag , a.ShopID , a.SKCCzpFlag , Case when a.SKCCzpFlag='爆' then 1 when a.SKCCzpFlag='畅' then 2  when a.SKCCzpFlag='平' then 3 when a.SKCCzpFlag='滞' then 4 else 5 end CzpOrderNum, a.ISbreakSize , a.ISbreakSize_light , a.SuppleDate,a.Saledays  FROM   ndw.ndw_SkcSaleRateRow a
                            LEFT JOIN ( 
                                          SELECT DISTINCT CompanyID , SuppleDate , ShopID ,  MaterialID ,  MoveOutFlag FROM  ndw.ndw_SuppleDetail
                                          WHERE CompanyID = '{UserInfo.UserCompanyId}' and MaterialID = '{MaterialID}' AND SuppleDate = '{Date:yyyy-MM-dd}'
                                        ) b ON a.CompanyID = b.CompanyID AND a.ShopID = b.ShopID AND a.MaterialID = b.MaterialID AND a.SuppleDate = b.SuppleDate
                            WHERE a.CompanyID = '{UserInfo.UserCompanyId}' and  a.MaterialID = '{MaterialID}' AND a.SuppleDate = '{Date:yyyy-MM-dd}' {(string.IsNullOrEmpty(ShopList) ? "" : $@"AND a.ShopID in ({ShopList})")}
                          ) rate ON stock.CompanyID = rate.CompanyID AND stock.StockID = rate.StockID AND stock.MaterialID = rate.MaterialID AND shop.ShopID = rate.ShopID
             WHERE  stock.CompanyID = '{UserInfo.UserCompanyId}' and stock.StockDate = '{Date.AddDays(-1):yyyy-MM-dd}'  AND rate.SuppleDate = '{Date:yyyy-MM-dd}'
             GROUP BY  stock.CompanyID ,stock.ExtrSource, stock.StockID , stock.MaterialID , stock.StockDate , shop.ShopID , shop.ShopName , shop.AreaID , material.MaterialName , rate.CalcSaleRate , rate.WeekSaleQty , rate.MoveOutFlag , rate.SKCCzpFlag , rate.CzpOrderNum , rate.ISbreakSize , rate.SuppleDate , rate.ISbreakSize_light,rate.Saledays
          ) table1
      ) table2
      LEFT JOIN ( 
                    SELECT  CompanyID , StockDate , MaterialID , SizeID , SizeName  FROM    nods.nods_erp_TmpCalcStock
                    WHERE   CompanyID = '{UserInfo.UserCompanyId}' AND MaterialID = '{MaterialID}'  AND StockDate = '{Date.AddDays(-1):yyyy-MM-dd}' AND IsShop = 1
                    GROUP BY CompanyID , StockDate , MaterialID , SizeID , SizeName
                 ) maxsize ON table2.CompanyID = maxsize.CompanyID  AND table2.StockDate = maxsize.StockDate AND table2.MaterialID = maxsize.MaterialID
      LEFT JOIN ( 
                    SELECT  CompanyID , StockID ,  MaterialID , StockDate , SizeID , CanUseQty , PathQty FROM    nods.nods_erp_TmpCalcStock
                    WHERE CompanyID = '{UserInfo.UserCompanyId}' and  MaterialID = '{MaterialID}' AND StockDate = '{Date.AddDays(-1):yyyy-MM-dd}'  AND IsShop = 1
                  ) stock ON table2.CompanyID = stock.CompanyID AND table2.StockID = stock.StockID AND table2.MaterialID = stock.MaterialID AND table2.StockDate = stock.StockDate AND maxsize.SizeID = stock.SizeID
      LEFT JOIN ( 
                    SELECT  CompanyID ,  ShopID ,  MaterialID ,  SizeID , MAX(MoveRequireQty) as MoveRequireQty FROM    ndw.ndw_AllocationRequire
                    WHERE CompanyID = '{UserInfo.UserCompanyId}' and  MaterialID = '{MaterialID}' AND Allocation_Date = '{Date:yyyy-MM-dd}'
                    GROUP BY CompanyID , ShopID , MaterialID , SizeID
                  ) RequireQty ON table2.CompanyID = RequireQty.CompanyID AND table2.ShopID = RequireQty.ShopID AND table2.MaterialID = RequireQty.MaterialID AND maxsize.SizeID = RequireQty.SizeID 
      LEFT JOIN ( 
                    SELECT  eba.ExtrSource,eba.AreaID , st.SizeID , st.SizeName , st.SizeCoreFlag  FROM    nods.nods_erp_Bas_Area eba
                    LEFT JOIN ndw.ndw_SizeType st ON st.ExtrSource = eba.ExtrSource and st.AreaID = eba.AreaID
                    WHERE eba.ExtrSource = '{UserInfo.UserExtrSource}' and eba.Level = 2
                  ) table3 ON table2.ExtrSource = table3.ExtrSource and table2.AreaID = table3.AreaID AND maxsize.SizeID = table3.SizeID
      LEFT JOIN ( 
                    SELECT  CompanyID , OutStockID , MaterialID , ExcelFlag   FROM    ngoods.ngoods_Mv_JobMessage
                    WHERE CompanyID = '{UserInfo.UserCompanyId}' and   MaterialID = '{MaterialID}' AND OperateDate = '{Date:yyyy-MM-dd}' {whereStr}
                    GROUP BY CompanyID , OutStockID , MaterialID , ExcelFlag
                  ) outStock ON table2.CompanyID = outStock.CompanyID AND table2.StockID = outStock.OutStockID  AND table2.MaterialID = outStock.MaterialID
      LEFT JOIN ( 
                    SELECT  table1.CompanyID , table1.InStockID , table1.MaterialID ,  table1.OutStockID , table1.ExcelFlag , table2.SizeID , table2.CanUseQty , shop.ShopID , shop.ShopName
                    FROM ( 
                            SELECT CompanyID , InStockID , MaterialID , OutStockID ,  ExcelFlag  FROM  ngoods.ngoods_Mv_JobMessage
                            WHERE CompanyID = '{UserInfo.UserCompanyId}' and  MaterialID = '{MaterialID}' AND OperateDate = '{Date:yyyy-MM-dd}' {whereStr}
                            GROUP BY CompanyID , InStockID , MaterialID , OutStockID , ExcelFlag
                          ) table1
                    INNER JOIN ( 
                                    SELECT CompanyID ,  StockID , MaterialID , SizeID , CanUseQty FROM nods.nods_erp_TmpCalcStock
                                    WHERE CompanyID = '{UserInfo.UserCompanyId}' and  MaterialID = '{MaterialID}'  AND StockDate = '{Date.AddDays(-1):yyyy-MM-dd}' AND IsShop = 1
                                  ) table2 ON table1.CompanyID = table2.CompanyID  AND table1.OutStockID = table2.StockID  AND table1.MaterialID = table2.MaterialID
                    INNER JOIN ndw.ndw_Shop shop ON table1.CompanyID = shop.CompanyID   AND table1.OutStockID = shop.StockID
                  ) inStock ON table2.CompanyID = inStock.CompanyID AND table2.StockID = inStock.InStockID  AND table2.MaterialID = inStock.MaterialID AND maxsize.SizeID = inStock.SizeID
WHERE  table2.CompanyID = '{UserInfo.UserCompanyId}' and table2.MaterialID = '{MaterialID}' AND table2.StockDate = '{Date.AddDays(-1):yyyy-MM-dd}' AND table2.SuppleDate = '{Date:yyyy-MM-dd}'
ORDER BY  CzpOrderNum, table2.CalcSaleRate desc, table2.StockID , inStock.OutStockID , stock.SizeID ";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                return ModelConvertHelper<NewOdsErpTmpCalcStockModel.ListModel>.ConvertToModel(dt).ToList();
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }


        #endregion

        #region ReturnOdsErpTmpCalcStockCount

        /// <summary>
        /// 
        /// </summary>
        /// <param name="MaterialID"></param>
        /// <param name="ShopList"></param>
        /// <param name="Date"></param>
        /// <returns></returns>
        public int ReturnOdsErpTmpCalcStockCount(string MaterialID, string ShopList, DateTime Date)
        {
            try
            {
                var sql = $@"SELECT count(1)
FROM ( 
        SELECT count(1)
        FROM ( 
                SELECT CompanyID ,ExtrSource, StockID ,  MaterialID , StockDate , MAX(PathQty) AS PathQty
                FROM nods.nods_erp_TmpCalcStock
                WHERE CompanyID = '{UserInfo.UserCompanyId}' and MaterialID = '{MaterialID}' AND StockDate = '{Date.AddDays(-1):yyyy-MM-dd}'  AND IsShop = 1
                GROUP BY  CompanyID ,ExtrSource,StockID ,MaterialID , StockDate
              ) stock
        INNER JOIN ( 
                        SELECT CompanyID ,  ShopID ,  ShopName ,  StockID , AreaID
                        FROM   ndw.ndw_Shop
                        WHERE CompanyID = '{UserInfo.UserCompanyId}' and  1 = 1 {(string.IsNullOrEmpty(ShopList) ? "" : $@"AND ShopID in ({ShopList})")}
                     ) shop ON stock.CompanyID = shop.CompanyID AND stock.StockID = shop.StockID
        INNER JOIN ( 
                        SELECT MaterialID , MaterialName
                        FROM   nods.nods_erp_SD_Mat_Material
                        WHERE ExtrSource = '{UserInfo.UserExtrSource}' and  MaterialID = '{MaterialID}' 
                     ) material ON stock.ExtrSource = material.ExtrSource and  stock.MaterialID = material.MaterialID
        INNER JOIN ( 
                      SELECT a.CompanyID , a.StockID , a.MaterialID , a.CalcSaleRate , a.WeekSaleQty , ISNULL(b.MoveOutFlag, 0) AS MoveOutFlag , a.ShopID , a.SKCCzpFlag , a.ISbreakSize , a.ISbreakSize_light , a.SuppleDate
                      FROM   ndw.ndw_SkcSaleRateRow a
                      LEFT JOIN ( 
                                    SELECT DISTINCT  CompanyID , SuppleDate , ShopID , MaterialID ,  MoveOutFlag
                                    FROM  ndw.ndw_SuppleDetail
                                    WHERE CompanyID = '{UserInfo.UserCompanyId}' and  MaterialID = '{MaterialID}' AND SuppleDate = '{Date:yyyy-MM-dd}' 
                                  ) b ON a.CompanyID = b.CompanyID  AND a.ShopID = b.ShopID AND a.MaterialID = b.MaterialID AND a.SuppleDate = b.SuppleDate
                      WHERE a.CompanyID = '{UserInfo.UserCompanyId}' and a.MaterialID = '{MaterialID}' AND a.SuppleDate = '{Date:yyyy-MM-dd}'  {(string.IsNullOrEmpty(ShopList) ? "" : $@"AND a.ShopID in ({ShopList})")}
                      ) rate ON stock.CompanyID = rate.CompanyID AND stock.StockID = rate.StockID  AND stock.MaterialID = rate.MaterialID  AND shop.ShopID = rate.ShopID
        WHERE stock.CompanyID = '{UserInfo.UserCompanyId}' and stock.StockDate = '{Date.AddDays(-1):yyyy-MM-dd}'  AND rate.SuppleDate = '{Date:yyyy-MM-dd}'
        GROUP BY  stock.CompanyID , stock.StockID , stock.MaterialID ,  stock.StockDate , stock.PathQty , shop.ShopID , shop.ShopName , shop.AreaID , material.MaterialName , rate.CalcSaleRate , rate.WeekSaleQty ,  rate.MoveOutFlag , rate.SKCCzpFlag ,  rate.ISbreakSize , rate.SuppleDate ,  rate.ISbreakSize_light
) table1";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Convert.ToInt32(Helper.ExecuteScalar(cmd));
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion

        #region ReturnTitle

        /// <summary>
        /// 返回调拨作业表头
        /// </summary>
        /// <param name="MaterialID"></param>
        /// <param name="Date"></param>
        /// <returns></returns>
        public IList<NewOdsErpTmpCalcStockModel.TitleModel> ReturnTitle(string MaterialID)
        {
            try
            {
                var sql = $@"select  stt.SizeID , stt.SizeName
from ndw.ndw_SKCSizeType sst
inner join ndw.ndw_SizeTextType stt ON sst.CompanyID = stt.CompanyID and sst.SizeTypeCode = stt.SizeTypeCode
inner join nods.nods_erp_SD_Mat_Size si ON stt.ExtrSource = si.ExtrSource and stt.SizeID = si.SizeID
inner join nods.nods_erp_SD_Mat_MaterialSize msi ON sst.ExtrSource = msi.ExtrSource and sst.MaterialID = msi.MaterialID AND si.SizeID = msi.SizeID
where sst.CompanyID = '{UserInfo.UserCompanyId}' and  sst.MaterialID = '{MaterialID}'
group by stt.SizeID , stt.SizeName ,  si.SizeCodeType ,  si.SizeCodeSort
order by si.SizeCodeType ,  CAST(si.SizeCodeSort as int)";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                return ModelConvertHelper<NewOdsErpTmpCalcStockModel.TitleModel>.ConvertToModel(dt);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion

        #region ReturnSeriesItem

        /// <summary>
        /// 根据货号返回系列，小品类
        /// </summary>
        /// <param name="MaterialID"></param>
        /// <returns></returns>
        public NewOdsErpTmpCalcStockModel.SeriesItemModel ReturnSeriesItem(string MaterialID)
        {
            try
            {
                var sql = $@"SELECT  series.SeriesName , item.ItemName
FROM nods.nods_erp_SD_Mat_Material material
INNER JOIN nods.nods_erp_SD_Mat_Series series ON material.ExtrSource = series.ExtrSource and material.SeriesID = series.SeriesID
INNER JOIN nods.nods_erp_SD_Mat_Item item ON material.ExtrSource = item.ExtrSource and material.ItemID = item.ItemID
WHERE material.ExtrSource = '{UserInfo.UserExtrSource}' and material.MaterialID = '{MaterialID}'
GROUP BY series.SeriesName , item.ItemName";
                var cmd = Helper.GetSqlStringCommand(sql);
                var ds = Helper.ExecuteDataSet(cmd);
                return ModelConvertHelper<NewOdsErpTmpCalcStockModel.SeriesItemModel>.DataSetToEntity(ds);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion

        #region UpdateMvJobMessage

        /// Writer：梁家健
        /// Create Date：2017-06-12
        [JsonType]
        [MustLogin]
        [Description("保存调出店铺")]
        [ReturnType(typeof(int), "0-成功")]
        public ApiResult UpdateMvJobMessage(GoodsMvJobMessageModel.ModifyModel param)
        {
            try
            {
                const string tableName = "ngoods.ngoods_Mv_JobMessage";
                const string sql =
                    @" (CompanyID='{0}' and MaterialID='{1}' and Operator='{2}' and OperateDate='{3}' and InStockID='{4}' and ExcelFlag!=1) ";
                if (param == null) return Error("参数对象为空");
                var firstSql =
                    $" DELETE  FROM  {tableName} WHERE extrsource='{UserInfo.UserExtrSource}' and  CompanyID = '{UserInfo.UserCompanyId}' and ";
                var sb = new StringBuilder();
                var finalSB = new StringBuilder(firstSql);
                if (param.list != null)
                {
                    var isExport = ReturnIsExport(param.MaterialID);
                    if (isExport != 1)
                    {
                        var count = 1;
                        var newList = new List<GoodsMvJobMessageModel.NeceModel>();
                        foreach (var item in param.list)
                        {
                            sb.AppendLine(
                                string.Format(sql, UserInfo.UserCompanyId, param.MaterialID, UserInfo.UserID,
                                    DateTime.Now.ToString("yyyy-MM-dd"), item.InStockID) +
                                ((count == param.list.Count || count % 500 == 0) ? ";" : " OR "));
                            if (count % 500 == 0)
                            {
                                finalSB.AppendLine(sb.ToString());
                                sb = new StringBuilder(firstSql);
                            }
                            else
                            {
                                if (count == param.list.Count)
                                {
                                    finalSB.AppendLine(sb.ToString());
                                    sb = new StringBuilder(firstSql);
                                }
                            }
                            if (item.OutStockIDList != null)
                            {
                                foreach (var it in item.OutStockIDList)
                                {
                                    var newItem = new GoodsMvJobMessageModel.NeceModel
                                    {
                                        ExtrSource = UserInfo.UserExtrSource,
                                        CompanyID = UserInfo.UserCompanyId,
                                        MaterialID = param.MaterialID,
                                        Operator = UserInfo.UserID,
                                        OperateDate = DateTime.Now.Date,
                                        InStockID = item.InStockID,
                                        OutStockID = it.OutStockID,
                                        ExcelFlag = 0L,
                                    };
                                    newList.Add(newItem);
                                }
                            }
                            count++;
                        }
                        var dt = DataTableHelper.LinqToDataTable(newList);
                        Helper.ExecuteSqlTran(dt, tableName, finalSB.ToString());
                        return Success(0);
                    }
                    else
                    {
                        return Error("该货号已经导出!");
                    }
                }
                return Success(0);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        #endregion

        #region ReturnAllMvJobMessage

        /// <summary>
        /// 返回所有调出信息
        /// </summary>
        /// <returns></returns>
        /// Writer：梁家健
        /// Create Date：2017-06-03
        public IList<GoodsMvJobMessageModel.NeceModel> ReturnAllMvJobMessage()
        {
            try
            {
                var sql =
                    $@"select * from ngoods.ngoods_Mv_JobMessage 
where  CompanyID = '{UserInfo.UserCompanyId}' and OperateDate='{DateTime.Now:yyyy-MM-dd}' and Operator='{UserInfo.UserID}' ";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                return ModelConvertHelper<GoodsMvJobMessageModel.NeceModel>.ConvertToModel(dt);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion

        #region ReturnIsExport

        /// <summary>
        /// 返回该货号是否已经导出
        /// </summary>
        /// <param name="MaterialID"></param>
        /// <returns></returns>
        public long ReturnIsExport(string MaterialID)
        {
            try
            {
                var sql = $@"SELECT  ISNULL(MAX(ExcelFlag), 0) ExcelFlag
FROM    ngoods.ngoods_Mv_JobMessage
WHERE CompanyID = '{UserInfo.UserCompanyId}' and   OperateDate = '{DateTime.Now:yyyy-MM-dd}' AND Operator = '{UserInfo.UserID}' AND MaterialID = '{MaterialID}' ";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Convert.ToInt64(Helper.ExecuteScalar(cmd));
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }


        #endregion

        #region ExportMvJobMessage

        /// <summary>
        /// 导出调出店铺
        /// </summary>
        /// <param name="Date"></param>
        /// <returns></returns>
        /// Writer：梁家健
        /// Create Date：2017-06-03
        public DataTable ExportMvJobMessage(string Keys)
        {
            try
            {
                var sql = $@"SELECT * from ngoods.nvw_goods_Mv_JobMessage
where companyid='{UserInfo.UserCompanyId}'  
{Keys}";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion

        #region ExportChange

        /// <summary>
        /// 更改导出状态
        /// </summary>
        /// Writer：梁家健
        /// Create Date：2017-06-06
        public void ExportChange(string Keys, DateTime Date)
        {
            try
            {
                var sql = $@"update ngoods.ngoods_Mv_JobMessage set ExcelFlag=1 
where ExtrSource='{UserInfo.UserExtrSource}' and CompanyID = '{UserInfo.UserCompanyId}' and OperateDate='{Date:yyyy-MM-dd}' {Keys}";
                var cmdList = new List<CommandInfo>
                {
                    new CommandInfo
                    {
                        CommandText = sql,
                    }
                };
                Helper.ExecuteSqlTran(cmdList);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion

        #region TotalMvJobMessage

        /// Writer：梁家健
        /// Create Date：2017-06-30
        [MustLogin]
        [Description("返回调拨结果")]
        [ReturnType(typeof(IList<GoodsMvJobMessageModel.ListModel>))]
        public ApiResult TotalMvJobMessage([ParamDescription("店铺")] string ShopIDs = "")
        {
            try
            {
                var shopList = string.IsNullOrEmpty(ShopIDs)
                    ? ""
                    : string.Join(",", ShopIDs.Split(',').Select(item => "'" + item + "'"));
                var dt = ReturnTotalMvJobMessage(shopList, $@" AND Operator = '{this.UserId}' ");
                var result = ModelConvertHelper<GoodsMvJobMessageModel.ListModel>.ConvertToModel(dt);
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        #endregion

        #region ReturnTotalMvJobMessage

        /// <summary>
        /// 返回调拨结果
        /// </summary>
        /// <returns></returns>
        /// Writer：梁家健
        /// Create Date：2017-06-30
        public DataTable ReturnTotalMvJobMessage(string ShopList, string whereStr)
        {
            try
            {
                var sql = $@"SELECT  shop.CompanyID ,shop.ShopID ,  shop.ShopName ,  shop.StockID , ( CASE WHEN outTable.outSum <= 0 THEN 0 ELSE outTable.outSum END ) AS outSum , ( CASE WHEN inTable.inSum <= 0 THEN 0  ELSE inTable.inSum END ) AS inSum , ( CASE WHEN outTable.outCount <= 0 THEN 0  ELSE outTable.outCount END ) AS outCountSKC , ( CASE  WHEN inTable.inCount <= 0 THEN 0 ELSE inTable.inCount  END ) AS inCountSKC
FROM ndw.ndw_Shop shop
INNER JOIN ngoods.ngoods_Mv_JobMessage mes 
ON shop.CompanyID = mes.CompanyID and shop.StockID = mes.OutStockID OR shop.StockID = mes.InStockID
LEFT JOIN ( 
             SELECT  CompanyID , OutStockID , COUNT(1) AS outCount , SUM(outSum) AS outSum
             FROM    ( 
                        SELECT mes.CompanyID , mes.OutStockID , mes.MaterialID , SUM(outStock.CanUseQty) AS outSum
                        FROM ngoods.ngoods_Mv_JobMessage mes
                        INNER JOIN nods.nods_erp_TmpCalcStock outStock 
                        ON outStock.CompanyID = mes.CompanyID and outStock.StockID = mes.OutStockID  AND outStock.MaterialID = mes.MaterialID AND outStock.CompanyID = mes.CompanyID
                        WHERE mes.CompanyID = '{UserInfo.UserCompanyId}' and  mes.OperateDate = '{DateTime.Now:yyyy-MM-dd}' AND outStock.StockDate = '{DateTime.Now.AddDays(-1):yyyy-MM-dd}' {whereStr}
                        GROUP BY  mes.CompanyID ,  mes.OutStockID ,   mes.MaterialID
                      ) outTable
              GROUP BY CompanyID ,  OutStockID
            ) outTable ON shop.CompanyID = outTable.CompanyID AND shop.StockID = outTable.OutStockID
LEFT JOIN ( 
            SELECT  CompanyID , InStockID , COUNT(1) AS inCount , SUM(inSum) AS inSum
            FROM ( 
                    SELECT mes.CompanyID , mes.InStockID , mes.MaterialID , SUM(inStock.CanUseQty) AS inSum
                    FROM ngoods.ngoods_Mv_JobMessage mes
                    INNER JOIN nods.nods_erp_TmpCalcStock inStock 
                    ON mes.CompanyID = '{UserInfo.UserCompanyId}' and inStock.StockID = mes.OutStockID  AND inStock.MaterialID = mes.MaterialID AND inStock.CompanyID = mes.CompanyID
                    WHERE mes.OperateDate = '{DateTime.Now:yyyy-MM-dd}' AND inStock.StockDate = '{DateTime.Now.AddDays(-1):yyyy-MM-dd}' {whereStr}
                    GROUP BY  mes.CompanyID ,mes.InStockID , mes.MaterialID
                   ) inTable
            GROUP BY CompanyID , InStockID
            ) inTable ON shop.CompanyID = inTable.CompanyID  AND shop.StockID = inTable.InStockID
WHERE shop.CompanyID = '{UserInfo.UserCompanyId}' and   mes.OperateDate = '{DateTime.Now:yyyy-MM-dd}' {(string.IsNullOrEmpty(ShopList) ? "" : $@"AND shop.ShopID in ({ShopList})")} {whereStr}
GROUP BY shop.CompanyID , shop.ShopID ,  shop.ShopName , shop.StockID , outTable.outSum , inTable.inSum , outTable.outCount , inTable.inCount
ORDER BY shop.CompanyID , shop.ShopID";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion

        #region GetStockSizeCanUseQty

        /// Writer：梁家健
        /// Create Date：2017-07-03
        [MustLogin]
        [Description("返回库存")]
        [ReturnType(typeof(IList<NewOdsErpTmpCalcStockModel.CoreModel>))]
        public ApiResult GetStockSizeCanUseQty([ParamDescription("公司ID")] string CompanyID = "",
            [ParamDescription("仓库ID")] string StockID = "", [ParamDescription("货号")] string MaterialID = "")
        {
            try
            {
                if (string.IsNullOrWhiteSpace(CompanyID)) return Error("请选择公司！");
                if (string.IsNullOrWhiteSpace(StockID)) return Error("请选择仓库！");
                if (string.IsNullOrWhiteSpace(MaterialID)) return Error("请选择仓库！");
                var dt = ReturnStockSizeCanUseQty(CompanyID, StockID, MaterialID);
                var result = ModelConvertHelper<NewOdsErpTmpCalcStockModel.CoreModel>.ConvertToModel(dt);
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        #endregion

        #region ReturnStockSizeCanUseQty

        /// <summary>
        /// 返回库存
        /// </summary>
        /// <param name="StockID"></param>
        /// <param name="CompanyID"></param>
        /// <param name="MaterialID"></param>
        /// <returns></returns>
        /// Writer：梁家健
        /// Create Date：2017-07-03
        public DataTable ReturnStockSizeCanUseQty(string CompanyID, string StockID, string MaterialID)
        {
            try
            {
                var sql = $@"SELECT  SizeID , ( CASE WHEN CanUseQty IS NULL THEN 0 WHEN CanUseQty < 0 THEN 0 ELSE CanUseQty END ) AS CanUseQty ,  SizeName , SizeTypeFlag
FROM    nODS.nods_erp_TmpCalcStock
WHERE CompanyID = '{UserInfo.UserCompanyId}'  AND ExtrSource = '{UserInfo.UserExtrSource}'  
and   MaterialID = '{MaterialID}' AND StockDate = '{DateTime.Now.AddDays(-1):yyyy-MM-dd}' AND StockID = '{StockID}'
";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion

    }
}
